﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using NyBio.Model;

namespace NyBio.DB
{
    public class TicketDB
    {
        private Connection connection;
        private ScheduleDB scheduleDB;
        private SeatDB seatDB;
        private UserDB userDB;

        public TicketDB()
        {
            connection = new Connection();
            scheduleDB = new ScheduleDB();
            seatDB = new SeatDB();
            userDB = new UserDB();
        }

        public List<Ticket> getTickets()
        {
            return where("");
        }

        public Ticket getTicketById(int id)
        {
            return singleWhere("xpTicket_id = " + id);
        }

        private Ticket singleWhere(String wClause)
        {
            Ticket ticket = null;

            string query = "SELECT * FROM XpTicket WHERE " + wClause;
            connection.Open();

            SqlDataReader reader = connection.Read(query);

            if (reader.Read())
            {
                ticket = new Ticket();
                reader.GetInt32(0);
                scheduleDB.getScheduleById(reader.GetInt32(1));
                seatDB.getSeatById(reader.GetInt32(2));
                userDB.getUserById(reader.GetInt32(3));
            }

            reader.Close();
            connection.Close();

            return ticket;
        }

        public List<Ticket> getTicketsBySchedule(int scheduleId)
        {
            return where("xpSchedule_id = " + scheduleId);
        }

        

        private List<Ticket> where(String wClause)
        {
            List<Ticket> tickets = new List<Ticket>();

            string query;
            if (!wClause.Equals(""))
                query = "SELECT * FROM XpTicket WHERE " + wClause;
            else
                query = "SELECT * FROM XpTicket";
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            while (reader.Read())
            {
                Ticket ticket = new Ticket();
                ticket._id = reader.GetInt32(0);
                ticket._scheduleId = reader.GetInt32(1);
                ticket._seatId = reader.GetInt32(2);
                ticket._userId = reader.GetInt32(3);
                tickets.Add(ticket);
            }

            reader.Close();
            connection.Close();

            return tickets;
        }

        public void removeTicket(int id)
        {
            string query = "DELETE FROM XpTicket WHERE xpTicket_id=" + id;
            connection.Open();
            connection.Execute(query);
            connection.Close();

        }

        public void insertTicket(Ticket ticket)
        {

            string query = @"INSERT INTO XpTicket (xpSchedule_id, " +
                "xpSeat_id, " + " xpUser_id, " + " price ) VALUES ( " + ticket._scheduleId + ", "
                + ticket._seatId + ", "
                + ticket._userId + ", "
                + ticket._price + ")";
            connection.Open();
            connection.Execute(query);
            connection.Close();

        }

        public void updateTicket(Ticket ticket)
        {
            string query = @"UPDATE XpTicket SET xpSchedule_id =" +
                ticket._scheduleId + ", xpSeat_id =" + ticket._seatId + ", xpUser_id =" + ticket._userId + ", price =" + ticket._price +
                " WHERE xpTicket_id=" + ticket._id;
            connection.Open();
            connection.Execute(query);
            connection.Close();

        }

        

        
    }
}
